         
/**
 * 
 *
 * @File name:  QueryDao.java 
 * @Create on:  2011-10-30 20:01:346
 * @Author   :  陈伟
 *
 * @ChangeList
 * ---------------------------------------------------
 * Date         Editor              ChangeReasons
 *
 */
           
package com.boho.estates.query.dao;

import java.sql.Connection;
import java.util.Date;
import java.util.List;

import cn.boho.framework.po.CQuery;
import cn.boho.framework.po.CQueryFactoryTool;
import cn.boho.framework.po.ComboPager;
import cn.boho.framework.po.DAO;
import cn.boho.framework.po.DynaBeanMap;
import cn.boho.framework.po.Pager;
            
/**
 * 【综合查询】的Dao操作类
 * 
 */
public class QueryDao extends DAO {
	
	
	/**
	* 分页查询所有【综合查询】信息
	* @param con
	* @param pager
	* @return
	* @throws Exception
	* @date 2011-10-30
	* @author 陈伟
	*
	*/
	public static ComboPager queryByPager(Connection con,Pager pager, Integer barCode, String name, Date d1, Date d2, double p1, double p2, 
			String type, String nature, Integer departNo, String manager, String warranty) throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select t1.FIXED_ASSETS_NAME, t1.FIXED_ASSETS_TYPE, t1.FIXED_ASSETS_NATURE, ");
		sql.append("t2.BARCODE, t2.STOCK_DATE, t2.STOCK_PRICE, t2.DEPARTMENT_NO, t2.WITH_MANANGER, t2.WARRANTY ");
		sql.append("from T_FIXED_ASSETS t1, T_FIXED_ASSETS_DETAIL t2 ");
		sql.append("where t1.FIXED_ASSETS_NO = t2.FIXED_ASSETS_NO and t1.IS_VALID = 1 and t2.IS_VALID = 1 ");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		//条形码，资产名称，购买日期，购买价格，资产类型，资产性质，所属部门，使用管理人，保修期
		int i = 0;
		if(barCode != -1){
			sql.append("and t2.BARCODE = ? ");
			query.setInt(++i, barCode);
		}
		if(!"".equals(name)){
			sql.append("and lower(t1.FIXED_ASSETS_NAME) like ? ");
			query.setString(++i, "%"+name.toLowerCase()+"%");
		}
		if(p1 != -1.0 && p2 != -1.0){
			sql.append("and t2.STOCK_PRICE >= ? and t2.STOCK_PRICE <= ? ");
			query.setDouble(++i, p1);
			query.setDouble(++i, p2);
		}
		else if(p1 != -1.0){
			sql.append("and t2.STOCK_PRICE >= ? ");
			query.setDouble(++i, p1);
		}
		else if(p2 != -1.0){
			sql.append("and t2.STOCK_PRICE <= ? ");
			query.setDouble(++i, p2);
		}
		if(!"".equals(type)){
			sql.append("and t1.FIXED_ASSETS_TYPE = ? ");
			query.setString(++i, type);
		}
		if(!"".equals(nature)){
			sql.append("and lower(t1.FIXED_ASSETS_NATURE) like ? ");
			query.setString(++i, "%"+nature.toLowerCase()+"%");
		}
		if(departNo != -1){
			sql.append("and t2.DEPARTMENT_NO = ? ");
			query.setInt(++i, departNo);
		}
		if(!"".equals(manager)){
			sql.append("and lower(t2.WITH_MANANGER) like ? ");
			query.setString(++i, "%"+manager.toLowerCase()+"%");
		}
		if(!"".equals(warranty)){
			sql.append("and warranty like ? ");
			query.setString(++i, "%"+warranty.toLowerCase()+"%");
		}
		sql.append("order by t2.CREATE_DATE desc");
		query.setCommand(sql.toString());
		return query.getDynaBeanMapComboPager("T_QUERY", pager,con);
	}
	
	public static DynaBeanMap getFixedAssetsById(Connection con, Integer fixedAssetsNo) throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select * from T_FIXED_ASSETS where IS_VALID = 1 and FIXED_ASSETS_NO = ?");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setInt(1, fixedAssetsNo);
		query.setCommand(sql.toString());
		return query.getDynaBeanMap("T_FIXED_ASSETS", con);
	}

	public static DynaBeanMap getFixedAssetsDetailByByBarCode(Connection con, Integer barCode) throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select * from T_FIXED_ASSETS_DETAIL where IS_VALID = 1 and BARCODE = ?");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setInt(1, barCode);
		query.setCommand(sql.toString());
		return query.getDynaBeanMap("T_FIXED_ASSETS_DETAIL", con);
	}
	
	public static List getFaChangeFlowByBarCode(Connection con, Integer barCode) throws Exception{
		StringBuffer sql = new StringBuffer();
		sql.append("select * from T_FA_CHANGE_FLOW where IS_VALID = 1 and BARCODE = ?");
		CQuery query = CQueryFactoryTool.createFactory().createCQuery();
		query.setInt(1, barCode);
		query.setCommand(sql.toString());
		return query.getDynaBeanMapList("T_FA_CHANGE_FLOW", con);
	}
	
}
